﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DA
{
   public class DBStock: IDisposable 
    {
          string sql;
        SqlHelpers sqlh;
        DataSet ds;
       // SqlDataReader dr;
        public DBStock()
        {
            sqlh = new SqlHelpers();
        }
        public DBStock(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }

        /// <summary>
        ///DB  自动产生进货单编号的方法
        /// </summary>
        /// <returns></returns>
        public string GetNewCG()
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@id", SqlDbType.VarChar, 20));
            pars[0].Direction = ParameterDirection.Output;
            sql = "pro_getNewCG";//执行存储过程
            sqlh.NonQuery (sql, pars, CommandType.StoredProcedure);
            return pars[0].Value.ToString();//返回参数的值
        }

        /// <summary>
        /// 插入进货单信息的方法
        /// </summary>
        /// <param name="s"></param>
        public void DBStockInssert(CStock s)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@sId", SqlDbType.VarChar, 20));
            pars[0].Value = s.CsId1 ;
            pars.Add(new SqlParameter("@sDate", SqlDbType.DateTime));
            pars[1].Value = s.CsDate1 ;
            pars.Add(new SqlParameter("@fsId", SqlDbType.Int));
            pars[2].Value = s.CfsId1 ;
            pars.Add(new SqlParameter("@eId", SqlDbType.Int));
            pars[3].Value = s.CeId1 ;
            pars.Add(new SqlParameter("@sMoney", SqlDbType.Float));
            pars[4].Value = s.CsMoney1  ;
            pars.Add(new SqlParameter("@sRemark", SqlDbType.VarChar, 50));
            pars[5].Value = s.SRemark ;
            sql = "insert into Stock values(@sId,@sDate,@fsId,@eId,@sMoney,@sRemark)";
            sqlh.ExcuteInsertUpdateDelete (sql, pars);
        }

        /// <summary>
        ///DB   查询所有进货单信息的方法
        /// </summary>
        /// <returns></returns>
        public DataSet DBStockQuery()
        {
            sql = "select s.sId,s.sDate,fs.fsName,e.eName,s.sMoney,s.sRemark from Employees e inner join Stock s on e.eId=s.eId inner join FillShops fs on s.fsId=fs.fsId";
            ds = sqlh.ExcuteSelect (sql, "ESF", null);
            return ds;
        }

        /// <summary>
        ///DB  根据进货单查询进货信息的方法
        /// </summary>
        /// <param name="sId">进货单编号</param>
        /// <returns></returns>
        public DataSet DBStockQueryXX(string sId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@sId", SqlDbType.VarChar, 20));
            pars[0].Value = sId;
            sql = "select s.sId,sb.msId,ms.msName,sb.msPrice,sb.msAmount,sb.msMoney,sb.msRemark from Menus ms inner join StockBillLists sb on ms.msId=sb.msId inner join Stock s on sb.sId=s.sId where s.sId=@sId";
            ds = sqlh.ExcuteSelect(sql, "ESF", pars );
            return ds;
      
        }

        /// <summary>
        /// DB  根据日期查询所有进货单信息的方法
        /// </summary>
        /// <param name="start">起始日期</param>
        /// <param name="end">截止日期</param>
        /// <returns></returns>
        public DataSet DBStockDateQuery(DateTime start, DateTime end)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@start", SqlDbType.DateTime));
            pars[0].Value = start;
            pars.Add(new SqlParameter("@end", SqlDbType.DateTime));
            pars[1].Value = end;
            sql = "select s.sId,s.sDate,fs.fsName,e.eName,s.sMoney,s.sRemark from Employees e inner join Stock s on e.eId=s.eId inner join FillShops fs on s.fsId=fs.fsId where s.sDate>=@start and s.sDate<=@end";
            ds = sqlh.ExcuteSelect(sql, "ESF", pars );
            return ds;
         
        }
    }
}
